import openpyxl
from openpyxl.utils import column_index_from_string


class SafeExcelUpdater:
    def __init__(self):
        self.stats = {
            'matched_ids': set(),
            'updated_cells': 0,
            'skipped_rows': 0,
            'matched_by_columns': {}  # 记录每个ID列的匹配情况
        }

    def safe_update(self,
                    source_file,
                    target_file,
                    source_id_cols,  # 源文件中所有可能的ID列数据类型，如['A','B','C']
                    target_id_col,  # 目标表中需要比对的ID列数据类型
                    source_price_col, #源小组表中价格数据类型
                    target_price_col, #活动表中价格数据类型
                    output_file=None,
                    header=True):
        """
        多ID列独立匹配版本
        - 源文件中任意ID列匹配目标表ID列即执行更新
        - 每个ID列的匹配情况会被单独统计
        """
        try:
            # 初始化匹配统计
            for col in source_id_cols:
                self.stats['matched_by_columns'][col] = 0

            # 只读模式打开源文件
            src_wb = openpyxl.load_workbook(source_file, read_only=True, data_only=True)
            tgt_wb = openpyxl.load_workbook(target_file)

            src_ws = src_wb.active
            tgt_ws = tgt_wb.active

            # 转换列标识
            src_id_indices = [self._col_to_index(col) for col in source_id_cols]
            tgt_id_index = self._col_to_index(target_id_col)
            src_price_index = self._col_to_index(source_price_col)
            tgt_price_index = self._col_to_index(target_price_col)

            # 阶段1：构建所有可能的ID到价格的映射
            id_to_price = {}
            start_row = 2 if header else 1

            for row in src_ws.iter_rows(min_row=start_row, values_only=True):
                price = row[src_price_index - 1] if len(row) >= src_price_index else None
                if price is None:
                    continue

                for col_idx, col in zip(src_id_indices, source_id_cols):
                    if col_idx - 1 < len(row):
                        cell_id = row[col_idx - 1]
                        if cell_id and cell_id not in id_to_price:  # 优先使用最先匹配到的值
                            id_to_price[cell_id] = price
                            self.stats['matched_by_columns'][col] += 1

            # 阶段2：更新目标文件
            for row in tgt_ws.iter_rows(min_row=start_row):
                if tgt_id_index - 1 < len(row):
                    target_id = row[tgt_id_index - 1].value
                    if not target_id:
                        self.stats['skipped_rows'] += 1
                        continue

                    if target_id in id_to_price:
                        new_price = id_to_price[target_id]
                        if tgt_price_index - 1 < len(row):
                            if row[tgt_price_index - 1].value != new_price:
                                row[tgt_price_index - 1].value = new_price
                                self.stats['matched_ids'].add(target_id)
                                self.stats['updated_cells'] += 1

            # 保存结果
            output_path = output_file or target_file
            tgt_wb.save(output_path)
            src_wb.close()

            # 生成统计报告
            match_report = "\n".join([f"- {col}列匹配: {count}次"
                                      for col, count in self.stats['matched_by_columns'].items()])

            print(
                f"操作完成！\n"
                f"匹配到 {len(self.stats['matched_ids'])} 个唯一ID\n"
                f"更新 {self.stats['updated_cells']} 处数据\n"
                f"跳过 {self.stats['skipped_rows']} 行空值\n"
                f"各ID列匹配情况:\n{match_report}\n"
                f"目标文件已保存到: {output_path}"
            )
            return True

        except Exception as e:
            print(f"操作失败: {str(e)}")
            return False

    def _col_to_index(self, col):
        """列标识转换工具"""
        if isinstance(col, str):
            return column_index_from_string(col.upper())
        return col


# 使用示例
if __name__ == '__main__':
    updater = SafeExcelUpdater()

    # 示例调用（修改为你的实际文件路径和列名）
    updater.safe_update(
        source_file='.venv/Reader/feishu_data.xlsx',
        target_file='.venv/Reader/',
        source_id_cols=['A', 'B', 'C'],  # 源文件的三列独立ID
        target_id_col='D',  # 目标表的ID列
        source_price_col='E',  # 源文件价格列
        target_price_col='F',  # 目标表价格列
        output_file='updated.xlsx',
        header=True
    )